package org.jeecg.modules.ws.util;

import cn.hutool.core.bean.BeanUtil;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import io.netty.util.internal.StringUtil;
import lombok.var;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecg.common.exception.JeecgBootException;
import org.jeecg.modules.sssj.dto.CwbbDto;
import org.jeecg.modules.ws.common.WsFileEnum;
import org.jeecg.modules.ws.dto.UploadYdbgDto;
import org.jeecg.modules.ws.dto.WsCwbbDto;
import org.jeecg.modules.ws.entity.*;
import org.jeecg.modules.ws.service.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 财务报表工具类
 *
 * @author ZJJ
 * @date 2025-02-28 15:05
 * @copyright Copyright (c) 2025 ZJJ
 */
@Component
public class CwbbExcelUtil {
    @Autowired
    private IWsZcfzbService zcfzbService;
    @Autowired
    private IWsLrbService lrbService;
    @Autowired
    private IWsXjllbService xjllbService;
    @Autowired
    private IWsYdbgService ydbgService;
    @Autowired
    private IWsCompanyService companyService;
    private static final String YDBG = "YDBG";
    private static final String CWBB = "CWBB";
    @Autowired
    private IWsFileService wsFileService;

    /**
     * 导入月度报告
     *
     * @param inputStream 输入流
     * @return 返回值
     */
    @Transactional(rollbackFor = Exception.class)
    public WsCompany importYdbg(InputStream inputStream, UploadYdbgDto dto) throws IOException {
        String companyName = dto.getCompanyName();
        String shxydm = dto.getShxydm();
        String year = dto.getYear();
        String month = dto.getMonth();
        //5.2、创建工作薄
        Workbook workbook = WorkbookFactory.create(inputStream);
        //5.3、获取工作薄里面sheet的个数
        int sheetNum = workbook.getNumberOfSheets();
        if (sheetNum != 1) {
            throw new JeecgBootException("暂时只支持单excel导入");
        }
        Sheet sheet = workbook.getSheetAt(0);
        //5.4.1、获取sheet中有数据的行数
        int rows = sheet.getPhysicalNumberOfRows();
        List<WsYdbg> list = new ArrayList<>();
        for (int j = 0; j < rows; j++) {
            //5.4.1.1、过滤掉文件的表头（视文件表头情况而定）
            if (j == 1 || j == 0 || j == 2 || j == 3) {
                continue;
            }
            // 5.4.1.2 获取当前行
            Row row = sheet.getRow(j);
            if (row == null) {
                // 如果行为空，跳过
                continue;
            }
            // 初始化变量
            String xm = null;
            String xh = null;
            Double bys = null;
            Double bnlj = null;
            Double sntq = null;
            // 获取单元格值并进行非空检查
            Cell cell;
            // 项目名称
            cell = row.getCell(0);
            xm = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 序号
            cell = row.getCell(1);
            xh = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 本月数
            cell = row.getCell(2);
            bys = (cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;
            // 年累计
            cell = row.getCell(3);
            bnlj = (cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;
            // 上年同期
            cell = row.getCell(4);
            sntq = (cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;
            // 创建实体对象
            WsYdbg ydbg = new WsYdbg();
            ydbg.setShxydm(shxydm);
            ydbg.setYear(year);
            ydbg.setMonth(month);
            ydbg.setXm(xm);
            ydbg.setXh(xh);
            ydbg.setBys(bys != null ? new BigDecimal(bys) : null);
            ydbg.setBnlj(bnlj != null ? new BigDecimal(bnlj) : null);
            ydbg.setSntq(sntq != null ? new BigDecimal(sntq) : null);
            list.add(ydbg);
        }
        workbook.close();
        ydbgService.deleteBySq(shxydm, year, month);
        ydbgService.saveBatch(list);
        WsCompany wsCompany = new WsCompany();
        wsCompany.setShxydm(shxydm);
        wsCompany.setCompanyName(companyName);
        wsCompany.setYear(year);
        wsCompany.setMonth(month);
        wsCompany.setType(YDBG);
        Wrapper<WsCompany> queryWrapper = Wrappers.<WsCompany>query().eq("shxydm", shxydm);
        companyService.saveOrUpdate(wsCompany, queryWrapper);
        return wsCompany;
    }


    /**
     * 导入资产负债表
     *
     * @return 返回值
     */
    public void importZcfzb(Sheet sheet, WsCwbbDto dto) throws IOException {
        String companyName = dto.getCompanyName();
        String shxydm = dto.getShxydm();
        Date skssqq = dto.getSkssqq();
        Date skssqz = dto.getSkssqz();
        //5.4.1、获取sheet中有数据的行数
        int rows = sheet.getPhysicalNumberOfRows();
        WsZcfzb zcfzb = new WsZcfzb();
        zcfzb.setShxydm(shxydm);
        zcfzb.setSkssqq(skssqq);
        zcfzb.setSkssqz(skssqz);
        zcfzb.setCompanyName(companyName);
        List<WsZcfzb> list = new ArrayList<>();
        for (int j = 0; j < rows; j++) {
            //5.4.1.1、过滤掉文件的表头（视文件表头情况而定）
            if (j == 1 || j == 0 || j == 2 || j == 3 || j == 4|| j == rows - 1) {
                continue;
            }
            //5.4.1.2、获取每一行的数据
            Row row = sheet.getRow(j);
            if (row == null) {
                // 如果行为空，跳过
                continue;
            }
            Cell cell;
            // 资产
            cell = row.getCell(0);
            String zcxmmc = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;// 项目名称
            cell = row.getCell(1);
            String zchc = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;// 行次
            cell = row.getCell(2);
            Double zcqmye = (cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;// 期末余额
            cell = row.getCell(3);
            Double zcqcye = (cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;// 期初余额
            // 权益
            cell = row.getCell(4);
            String qyxmmc = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;// 项目名称
            cell = row.getCell(5);
            String qyhc = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;// 行次
            cell = row.getCell(6);
            Double qyqmye = (cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;// 期末余额
            cell = row.getCell(7);
            Double qyqcye = (cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;// 期初余额
            WsZcfzb zcfzb1 = new WsZcfzb();
            BeanUtil.copyProperties(zcfzb, zcfzb1);
            zcfzb1.setXmmc(zcxmmc);
            zcfzb1.setHc(Integer.valueOf(zchc));
            zcfzb1.setQmye(zcqmye != null ? BigDecimal.valueOf(zcqmye) : null);
            zcfzb1.setQcye(zcqcye != null ?BigDecimal.valueOf(zcqcye) : null);
            zcfzb1.setZcfzLx("ZC");
            WsZcfzb zcfzb2 = new WsZcfzb();
            BeanUtil.copyProperties(zcfzb, zcfzb2);
            zcfzb2.setXmmc(qyxmmc);
            zcfzb2.setHc(Integer.valueOf(qyhc));
            zcfzb2.setQmye(qyqmye != null ? BigDecimal.valueOf(qyqmye) : null);
            zcfzb2.setQcye(qyqcye != null ? BigDecimal.valueOf(qyqcye) : null);
            zcfzb2.setZcfzLx("FZ");
            list.add(zcfzb1);
            list.add(zcfzb2);
        }
        zcfzbService.deleteBySq(shxydm, skssqq, skssqz);
        zcfzbService.saveBatch(list);
    }

    /**
     * 导入利润表
     *
     * @return 返回值
     */
    public void importLrb(Sheet sheet, WsCwbbDto dto) throws IOException {
        String companyName = dto.getCompanyName();
        String shxydm = dto.getShxydm();
        Date skssqq = dto.getSkssqq();
        Date skssqz = dto.getSkssqz();
        WsLrb lrb = new WsLrb();
        lrb.setShxydm(shxydm);
        lrb.setSkssqq(skssqq);
        lrb.setSkssqz(skssqz);
        lrb.setCompanyName(companyName);
        List<WsLrb> list = new ArrayList<>();
        //5.4.1、获取sheet中有数据的行数
        int rows = sheet.getPhysicalNumberOfRows();
        for (int j = 0; j < rows; j++) {
            //5.4.1.1、过滤掉文件的表头（视文件表头情况而定）
            if (j == 1 || j == 0 || j == 2 || j == 3) {
                continue;
            }
            //5.4.1.2、获取每一行的数据
            Row row = sheet.getRow(j);
            if (row == null) {
                // 如果行为空，跳过
                continue;
            }
            Cell cell;
            cell = row.getCell(0);
            String xmmc1 =(cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;// 项目名称
            cell = row.getCell(1);
            String hc1 = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;// 行次
            cell = row.getCell(2);
            Double bqje1 =(cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;// 本期金额
            cell = row.getCell(3);
            Double sqje1 = (cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;// 上期金额
            cell = row.getCell(4);
            String xmmc2 = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;// 项目名称
            cell = row.getCell(5);
            String hc2 = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;// 行次
            cell = row.getCell(6);
            Double bqje2 = (cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;// 本期金额
            cell = row.getCell(7);
            Double sqje2 = (cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;// 上期金额
            WsLrb lrb1 = new WsLrb();
            BeanUtil.copyProperties(lrb, lrb1);
            lrb1.setXmmc(xmmc1);
            lrb1.setHc(Integer.valueOf(hc1));
            lrb1.setBqje(bqje1 != null ? BigDecimal.valueOf(bqje1) : null);
            lrb1.setSqje(sqje1 != null ? BigDecimal.valueOf(sqje1) : null);
            WsLrb lrb2 = new WsLrb();
            BeanUtil.copyProperties(lrb, lrb2);
            lrb2.setXmmc(xmmc2);
            lrb2.setHc(Integer.valueOf(hc2));
            lrb2.setBqje(bqje2 != null ? BigDecimal.valueOf(bqje2) : null);
            lrb2.setSqje(sqje2 !=null ? BigDecimal.valueOf(sqje2) : null);
            list.add(lrb1);
            list.add(lrb2);
        }
        lrbService.deleteBySq(shxydm, skssqq, skssqz);
        lrbService.saveBatch(list);
    }

    /**
     * 导入现金流量表
     *
     * @return 返回值
     */
    public void importXjllb(Sheet sheet,WsCwbbDto dto) throws IOException {
        String shxydm = dto.getShxydm();
        Date skssqq = dto.getSkssqq();
        Date skssqz = dto.getSkssqz();
        String companyName = dto.getCompanyName();
        List<WsXjllb> list = new ArrayList<>();
        WsXjllb xjllb = new WsXjllb();
        xjllb.setShxydm(shxydm);
        xjllb.setSkssqq(skssqq);
        xjllb.setSkssqz(skssqz);
        xjllb.setCompanyName(companyName);
        //5.4.1、获取sheet中有数据的行数
        int rows = sheet.getPhysicalNumberOfRows();
        for (int j = 0; j < rows; j++) {
            //5.4.1.1、过滤掉文件的表头（视文件表头情况而定）
            if (j == 1 || j == 0 || j == 2 || j == 3) {
                continue;
            }
            //5.4.1.2、获取每一行的数据
            Row row = sheet.getRow(j);
            if (row == null) {
                // 如果行为空，跳过
                continue;
            }
            Cell cell;
            cell = row.getCell(0);
            String xmmc1 = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;// 项目名称
            cell = row.getCell(1);
            String hc1 = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;// 行次
            cell = row.getCell(2);
            Double bqje1 = (cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;// 本期金额
            cell = row.getCell(3);
            Double sqje1 = (cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;// 上期金额
            cell = row.getCell(4);
            String xmmc2 = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;// 项目名称
            cell = row.getCell(5);
            String hc2 = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;// 行次
            cell = row.getCell(6);
            Double bqje2 = (cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;// 本期金额
            cell = row.getCell(7);
            Double sqje2 = (cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;// 上期金额
            WsXjllb xjllb1 = new WsXjllb();
            BeanUtil.copyProperties(xjllb, xjllb1);
            xjllb1.setXmmc(xmmc1);
            xjllb1.setHc(Integer.valueOf(hc1));
            xjllb1.setBqje(bqje1 != null ? BigDecimal.valueOf(bqje1) : null);
            xjllb1.setSqje(sqje1 != null ? BigDecimal.valueOf(sqje1) : null);
            WsXjllb xjllb2 = new WsXjllb();
            BeanUtil.copyProperties(xjllb, xjllb2);
            xjllb2.setXmmc(xmmc2);
            xjllb2.setHc(Integer.valueOf(hc2));
            xjllb2.setBqje(bqje2 != null ? BigDecimal.valueOf(bqje2): null);
            xjllb2.setSqje(sqje2 != null ?BigDecimal.valueOf(sqje2) : null);
            list.add(xjllb1);
            list.add(xjllb2);
        }
        xjllbService.deleteBySq(shxydm, skssqq, skssqz);
        xjllbService.saveBatch(list);
    }

    /**
     * 判断字符串是否不为空并且是数值类型
     *
     * @param str 待判断的字符串
     * @return 如果字符串不为空且是数值类型，返回true；否则返回false
     */
    public boolean isNumeric(String str) {
        if (StringUtil.isNullOrEmpty(str)) {
            return false;
        }
        try {
            new BigDecimal(str);
            return true;
        } catch (NumberFormatException e) {
            return false;
        }
    }

    /**
     * 导入财务报表
     *
     * @param inputStream 输入流
     * @return 返回值
     */
    @Transactional(rollbackFor = Exception.class)
    public WsCompany importCwbb(InputStream inputStream, WsCwbbDto dto) throws Exception {
        String companyName = dto.getCompanyName();
        String shxydm = dto.getShxydm();
        //5.2、创建工作薄
        Workbook workbook = WorkbookFactory.create(inputStream);
        //5.3、获取工作薄里面sheet的个数
        int sheetNum = workbook.getNumberOfSheets();
        if (sheetNum != 3) {
            throw new JeecgBootException("文件需要根据模板样例导入");
        }
        // 导入资产负债表
        Sheet zcfzb = workbook.getSheetAt(0);
        importZcfzb(zcfzb, dto);
        // 导入利润表
        Sheet lrb = workbook.getSheetAt(1);
        importLrb(lrb, dto);
        // 导入现金流量表
        Sheet xjllb = workbook.getSheetAt(2);
        importXjllb(xjllb, dto);
        workbook.close();
        WsCompany wsCompany = new WsCompany();
        wsCompany.setShxydm(shxydm);
        wsCompany.setCompanyName(companyName);
        wsCompany.setSkssqq(dto.getSkssqq());
        wsCompany.setSkssqz(dto.getSkssqz());
        wsCompany.setType(CWBB);
        Wrapper<WsCompany> queryWrapper = Wrappers.<WsCompany>query().eq("shxydm", shxydm);
        companyService.saveOrUpdate(wsCompany, queryWrapper);
        String bizTableId = wsCompany.getId();
        WsFile zcfzbFile = wsFileService.uploadFile(convertSheetToMultipartFile(zcfzb, "资产负债表"), WsFileEnum.WS_CWBB_ZCFZB.getBizName(), bizTableId, "zcfzb");
        wsCompany.setZcfzbFileId(zcfzbFile.getId());
        WsFile lrbFile = wsFileService.uploadFile(convertSheetToMultipartFile(lrb, "利润表"), WsFileEnum.WS_CWBB_LRB.getBizName(), bizTableId, "lrb");
        wsCompany.setLrbFileId(lrbFile.getId());
        WsFile xjllbFile = wsFileService.uploadFile(convertSheetToMultipartFile(xjllb, "现金流量表"), WsFileEnum.WS_CWBB_XJLLB.getBizName(), bizTableId, "xjllb");
        wsCompany.setXjllbFileId(xjllbFile.getId());
        companyService.updateById(wsCompany);
        return wsCompany;
    }

    public MultipartFile convertSheetToMultipartFile(Sheet originalSheet, String fileName) throws IOException {
        Workbook workbook;

        // 根据Sheet类型创建新Workbook
        if (originalSheet instanceof HSSFSheet) {
            workbook = new HSSFWorkbook();
        } else {
            workbook = new XSSFWorkbook();
        }

        Sheet newSheet = workbook.createSheet(originalSheet.getSheetName());

        // 复制行和单元格数据
        for (Row row : originalSheet) {
            Row newRow = newSheet.createRow(row.getRowNum());
            for (Cell cell : row) {
                Cell newCell = newRow.createCell(cell.getColumnIndex());
                switch (cell.getCellType()) {
                    case STRING:
                        newCell.setCellValue(cell.getStringCellValue());
                        break;
                    case NUMERIC:
                        newCell.setCellValue(cell.getNumericCellValue());
                        break;
                    case BOOLEAN:
                        newCell.setCellValue(cell.getBooleanCellValue());
                        break;
                    case FORMULA:
                        newCell.setCellFormula(cell.getCellFormula());
                        break;
                    case BLANK:
                        newCell.setBlank();
                        break;
                    default:
                        newCell.setCellValue(cell.toString());
                        break;
                }
            }
        }

        // 写入字节流
        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            workbook.write(outputStream);
            byte[] bytes = outputStream.toByteArray();

            // 使用自定义 MultipartFile 实现
            String contentType = (originalSheet instanceof HSSFSheet) ?
                    "application/vnd.ms-excel" :
                    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            return new CustomMultipartFile(bytes, "file", fileName, contentType);
        } finally {
            workbook.close();
        }
    }
}
